package com.situ.web.daoImpl;
//package com.situ.web.daoImpl;
//
//import java.sql.Connection;
//import java.sql.PreparedStatement;
//import java.sql.ResultSet;
//import java.sql.SQLException;
//import java.util.ArrayList;
//import java.util.List;
//import java.util.Map;
//
//import com.situ.web.entity.Department;
//import com.situ.web.entity.Role;
//import com.situ.web.entity.User;
//import com.situ.web.mapper.IUserMapper;
//import com.situ.web.util.JDBCUtil;
//import com.situ.web.util.ModelConvertUtil;
//
//public  class UserDaoImpl implements IUserMapper{
//
//	@Override
//	public Integer getTotalCount() {
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		int count = 0;
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "SELECT count(*) FROM user";
//			preparedStatement = connection.prepareStatement(sql);
//			resultSet = preparedStatement.executeQuery();
//			if (resultSet.next()) {
//				count = resultSet.getInt(1); // 获取第一列数据
//			}
//			
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, resultSet);
//		}
//		
//		return count;
//	}
//
////	@Override
////	public List<Map<String, Object>> pageList(map) {
////		Connection connection = null;
////		PreparedStatement preparedStatement = null;
////		ResultSet resultSet = null;
////		List<Map<String, Object>> list = null;
////		try {
////			connection = JDBCUtil.getConnection();
////			String sql = "SELECT u.id,u.name,u.email,r.name AS 'roleName',d.name AS 'departmentName',u.age\r\n" + 
////					"FROM `user` AS u INNER JOIN department AS d\r\n" + 
////					"ON u.department_id=d.id\r\n" + 
////					"INNER JOIN user_role AS ur\r\n" + 
////					"ON u.id=ur.user_id\r\n" + 
////					"INNER JOIN role AS r\r\n" + 
////					"ON ur.role_id=r.id\r\n" + 
////					"ORDER BY u.id ASC LIMIT ?,?";
////			preparedStatement = connection.prepareStatement(sql);
////			preparedStatement.setInt(1, offset);
////			preparedStatement.setInt(2, pageSize);
////			System.out.println(preparedStatement);
////			resultSet = preparedStatement.executeQuery();
////			list = ModelConvertUtil.converToList(resultSet);
////		} catch (SQLException e) {
////			e.printStackTrace();
////		} finally {
////			JDBCUtil.close(connection, preparedStatement, resultSet);
////		}
////		
////		return list;
////	}
//
//	@Override
//	public List<Map<String, Object>> search(String searchName) {
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		List<Map<String, Object>> list = null;
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "SELECT u.id,u.name,u.email,r.name AS 'roleName',d.name AS 'departmentName',u.age\r\n" + 
//					"FROM `user` AS u INNER JOIN department AS d\r\n" + 
//					"ON u.department_id=d.id\r\n" + 
//					"INNER JOIN user_role AS ur\r\n" + 
//					"ON u.id=ur.user_id\r\n" + 
//					"INNER JOIN role AS r\r\n" + 
//					"ON ur.role_id=r.id\r\n" + 
//					"WHERE u.name LIKE ?";
//			preparedStatement = connection.prepareStatement(sql);
//			preparedStatement.setString(1, "%" + searchName + "%");
//			System.out.println(preparedStatement);
//			resultSet = preparedStatement.executeQuery();
//			list = ModelConvertUtil.converToList(resultSet);
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, resultSet);
//		}
//		return list;
//	}
//
//	@Override
//	public int deleteById(Integer id) {
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		int count = 0;
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "DELETE FROM user_role WHERE user_id=?;DELETE FROM user WHERE id=?";
//			preparedStatement = connection.prepareStatement(sql);
//			preparedStatement.setInt(1, id);
//			preparedStatement.setInt(2, id);
//			System.out.println(preparedStatement);
//			count = preparedStatement.executeUpdate();
//			
//			if (count == 1) {
//				System.out.println("删除成功");
//			} else {
//				System.out.println("删除失败");
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, null);
//		}
//	return count;
//	}
//
//	@Override
//	public User selectById(int id) {
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		User user = null;
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "SELECT id,`name`,password,email,department_id,age FROM `user` WHERE id=?";
//			preparedStatement = connection.prepareStatement(sql);
//			preparedStatement.setInt(1, id);
//			resultSet = preparedStatement.executeQuery();
//			
//			if (resultSet.next()) {
//				int userId = resultSet.getInt("id");
//				String name = resultSet.getString("name");
//				String password = resultSet.getString("password");
//				String email = resultSet.getString("email");
//				int department_id = resultSet.getInt("department_id");
//				int age = resultSet.getInt("age");
//				user = new User(id, name, password, email, department_id, age);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return user;
//	}
//
//	@Override
//	public int update(User user) {
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		int count = 0;
//		
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "update `user` SET `name`=?,password=?,email=?,department_id=?,age=? WHERE id=?";
//			preparedStatement = connection.prepareStatement(sql);
//			preparedStatement.setString(1, user.getName());
//			preparedStatement.setString(2, user.getPassword());
//			preparedStatement.setString(3, user.getEmail());
//			preparedStatement.setInt(4, user.getDepartment_id());
//			preparedStatement.setInt(5, user.getAge());
//			preparedStatement.setInt(6, user.getId());
//			System.out.println(preparedStatement);
//			count = preparedStatement.executeUpdate();
//			if (count == 1) {
//				System.out.println("更新成功");
//			} else {
//				System.out.println("更新失败");
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, null);
//		}
//		return count;
//	}
//
//	@Override
//	public List selectByName(String searchName) {
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		ArrayList<User> list = new ArrayList<User>();
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "SELECT * FROM user WHERE name=?";
//			preparedStatement = connection.prepareStatement(sql);
//			preparedStatement.setString(1, searchName);
//			System.out.println(preparedStatement);
//			resultSet = preparedStatement.executeQuery();
//			while (resultSet.next()) {
//				int id = resultSet.getInt("id");
//				String name = resultSet.getString("name");
//				String password = resultSet.getString("password");
//				String email = resultSet.getString("email");
//				int department_id = resultSet.getInt("department_id");
//				int age = resultSet.getInt("age");
//				User user = new User(id, name, password, email, department_id, age);
//				list.add(user);
//			}
//
//			for (User user : list) {
//				System.out.println(user);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, resultSet);
//		}
//		return list;
//	}
//
//	@Override
//	public int insert(User user) {
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		int count = 0;
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "INSERT INTO user(`name`,password, email, department_id, age) VALUES(?,?,?,?,?)";
//			preparedStatement = connection.prepareStatement(sql);
//			preparedStatement.setString(1, user.getName());
//			preparedStatement.setString(2, user.getPassword());
//			preparedStatement.setString(3, user.getEmail());
//			preparedStatement.setInt(4, user.getDepartment_id());
//			preparedStatement.setInt(5, user.getAge());
//			System.out.println(preparedStatement);
//			count = preparedStatement.executeUpdate();
//			if (count == 1) {
//				System.out.println("插入成功");
//			} else {
//				System.out.println("插入失败");
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, null);
//		}
//		return count;
//	}
//
//	@Override
//	public List selectRole() {
//		ArrayList<Role> list = new ArrayList<Role>();
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "SELECT id,`name` FROM role";
//			preparedStatement = connection.prepareStatement(sql);
//			resultSet = preparedStatement.executeQuery();
//			while (resultSet.next()) {
//				int id = resultSet.getInt("id");
//				String name = resultSet.getString("name");
//				Role role = new Role(id, name);
//				list.add(role);
//			}
//
//			for (Role role : list) {
//				System.out.println(role);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, resultSet);
//		}
//		return list;
//	}
//
//	@Override
//	public List selectDepartment() {
//		ArrayList<Department> list = new ArrayList<Department>();
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		try {
//			connection = JDBCUtil.getConnection();
//			String sql = "SELECT id,`name` FROM department";
//			preparedStatement = connection.prepareStatement(sql);
//			resultSet = preparedStatement.executeQuery();
//			while (resultSet.next()) {
//				int id = resultSet.getInt("id");
//				String name = resultSet.getString("name");
//				Department department = new Department(id, name);
//				list.add(department);
//			}
//
//			for (Department department : list) {
//				System.out.println(department);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			JDBCUtil.close(connection, preparedStatement, resultSet);
//		}
//		return list;
//	}
//
//	@Override
//	public List pageList(Map map) {
//		// TODO Auto-generated method stub
//		return null;
//	}
//
//
//}
